package cn.hpclub.server.service;/**
 * Created by zenghl on 2016/8/23.
 */

import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @FileName BroadbandOpenOrderService
 * @Author zhl
 * @Description
 * @Date 2016-08-23 12:32
 * @CopyRight ZTE Corporation
 */
public class CmccCardOrderService {

    private static final Logger logger = LoggerFactory.getLogger(CmccCardOrderService.class);
    public static CmccCardOrderService service = new CmccCardOrderService();



    public String getSqlHead(){
        String sqlHead = "SELECT name,phone_no,emp_code,card_phone_no,card_number,business_name,business_code,created_time";
        return sqlHead;
    }


    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " FROM cmcc_card_order WHERE 1=1 ";
        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForCount(item);
        }
        sqlExceptSelect += " ORDER BY created_time DESC ";
        return sqlExceptSelect;
    }



    private String buildQueryForCount(Map.Entry<String, String> item){
        if("start_time".equalsIgnoreCase(item.getKey())){
            String start_time = "'" + item.getValue().replaceAll(" ", "") + " 00:00:00'";
            return " and last_updated_time >=" + start_time + " ";
        }
        if("end_time".equalsIgnoreCase(item.getKey())){
            String end_time = "'" + item.getValue().replaceAll(" ", "") + " 23:59:59'";
            return " and last_updated_time <=" + end_time + " ";
        }
        /*if("city_id".equalsIgnoreCase(item.getKey())){
            String city_id = "" + item.getValue().replaceAll(" ", "") + "";
            return " and city_id =" + city_id + " ";
        }*/
        return " and " + item.getKey() + " like '%" + item.getValue() + "%'";
    }
    /**
     * @describe
     * @param searchInfo 查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql = getSqlHead() + getSqlExceptSelect(searchInfo);
        List<Record> seq = Db.find(sql);
        String[] excelHeaders = new String[]{"姓名","手机号","工号","写卡号码","写白卡号码","写卡资费名称","写卡资费代码","写卡时间"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"name","phone_no","emp_code","card_phone_no","card_number","business_name","business_code","created_time"
                };
       try {
            exportUtils.exportExcel(keys,seq,"yyyy-MM-dd HH:mm:ss",1);//此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("CmccCardOrderService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"+e.getMessage());
        } catch (IOException ex) {
            logger.error("CmccCardOrderService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"+ex.getMessage());
        }
        return workbook;
    }
}
